const pool = require('./db');

// 初始化数据库并插入测试数据
const setup = async () => {
  const conn = await pool.getConnection();
  try {
    console.log('开始初始化数据库...');
    
    // 创建表
    await conn.query(`
      CREATE TABLE IF NOT EXISTS songs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        artist VARCHAR(255) NOT NULL,
        lyrics TEXT,
        song_url VARCHAR(255) NOT NULL,
        cover_url VARCHAR(255),
        duration INT DEFAULT 0,
        play_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`);
    
    await conn.query(`
      CREATE TABLE IF NOT EXISTS playlists (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT,
        cover_url VARCHAR(255),
        is_public BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`);
    
    await conn.query(`
      CREATE TABLE IF NOT EXISTS playlist_songs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        playlist_id INT NOT NULL,
        song_id INT NOT NULL,
        sort_order INT DEFAULT 0,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        UNIQUE KEY unique_playlist_song (playlist_id, song_id)
      )`);
    
    await conn.query(`
      CREATE TABLE IF NOT EXISTS user_favorites (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(100) NOT NULL,
        song_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_song (user_id, song_id)
      )`);
    
    await conn.query(`
      CREATE TABLE IF NOT EXISTS play_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(100) NOT NULL,
        song_id INT NOT NULL,
        played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE
      )`);
    
    console.log('数据库表创建成功！');
    
    // 检查是否已有数据
    const [existingSongs] = await conn.execute('SELECT COUNT(*) as count FROM songs');
    if (existingSongs[0].count > 0) {
      console.log(`数据库已有 ${existingSongs[0].count} 首歌曲，跳过数据插入`);
      conn.release();
      process.exit(0);
      return;
    }
    
    // 插入测试歌曲
    console.log('开始插入测试数据...');
    const testSongs = [
      {
        title: '夜空中最亮的星',
        artist: '逃跑计划',
        lyrics: '夜空中最亮的星 能否听清\n那仰望的人 心底的孤独和叹息',
        song_url: 'https://music.163.com/song/media/outer/url?id=25706282.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/647cde825adf712e96b3bc66cb67656c.jpg',
        duration: 240
      },
      {
        title: '海阔天空',
        artist: 'Beyond',
        lyrics: '今天我 寒夜里看雪飘过\n怀着冷却了的心窝漂远方',
        song_url: 'https://music.163.com/song/media/outer/url?id=346576.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/c259606553810742eff43369376a2a87.jpg',
        duration: 320
      },
      {
        title: '起风了',
        artist: '买辣椒也用券',
        lyrics: '这一路上走走停停\n顺着少年漂流的痕迹',
        song_url: 'https://music.163.com/song/media/outer/url?id=1330348068.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/5664d87772b76472ab0cd4f1d1a44d1d.jpg',
        duration: 280
      },
      {
        title: '稻香',
        artist: '周杰伦',
        lyrics: '对这个世界如果你有太多的抱怨\n跌倒了就不敢继续往前走',
        song_url: 'https://music.163.com/song/media/outer/url?id=185908.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/dceca67b9c576b6031ddcfce7b27afb8.jpg',
        duration: 220
      },
      {
        title: '晴天',
        artist: '周杰伦',
        lyrics: '故事的小黄花\n从出生那年就飘着',
        song_url: 'https://music.163.com/song/media/outer/url?id=186016.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/0d326ebc29d14771873128d2e0f564fd.jpg',
        duration: 260
      },
      {
        title: '演员',
        artist: '薛之谦',
        lyrics: '简单点 说话的方式简单点',
        song_url: 'https://music.163.com/song/media/outer/url?id=32507038.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/647cde825adf712e96b3bc66cb67656c.jpg',
        duration: 260
      },
      {
        title: '告白气球',
        artist: '周杰伦',
        lyrics: '塞纳河畔 左岸的咖啡',
        song_url: 'https://music.163.com/song/media/outer/url?id=436514312.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/0d326ebc29d14771873128d2e0f564fd.jpg',
        duration: 210
      },
      {
        title: '南山南',
        artist: '马頔',
        lyrics: '你在南方的艳阳里 大雪纷飞',
        song_url: 'https://music.163.com/song/media/outer/url?id=29722263.mp3',
        cover_url: 'https://ai-public.mastergo.com/ai/img_res/5664d87772b76472ab0cd4f1d1a44d1d.jpg',
        duration: 290
      }
    ];

    for (const song of testSongs) {
      await conn.execute(
        'INSERT INTO songs (title, artist, lyrics, song_url, cover_url, duration) VALUES (?, ?, ?, ?, ?, ?)',
        [song.title, song.artist, song.lyrics, song.song_url, song.cover_url, song.duration]
      );
    }

    console.log(`成功插入 ${testSongs.length} 首测试歌曲！`);
    console.log('数据库初始化完成！');
    
  } catch (error) {
    console.error('初始化失败:', error);
  } finally {
    conn.release();
    process.exit(0);
  }
};

setup();
